--    Author    : ChenErHao
--    Name      : DM.T_MTH_CURRENT_M_AMONT.HQL
--    Functions : 利率-按当月发生业务计算
--    Purpose   :
--    Revisions or Comments
--    VER        DATE        AUTHOR           DESCRIPTION
--   ---------  ----------  ---------------  ------------------------------------
--    1.0        2019-01-23  ChenErHao       1.CREATE THE PROCEDURE
--

INSERT OVERWRITE TABLE DM.T_MTH_CURRENT_M_AMONT PARTITION (FISCAL_MTH_DIMNSN_ID = '#V_DATA_MONTH#')
 select 
   nvl(AREA_NO_ID,9999), 
   nvl(DEP_AMOUNT,0) DEP_AMOUNT, 
   nvl(DEP_AMOUNT_AMT,0) DEP_AMOUNT_AMT, 
   nvl(LOAN_AMOUNT,0) LOAN_AMOUNT, 
   nvl(LOAN_AMOUNT_AMT,0) LOAN_AMOUNT_AMT, 
   nvl(DEP_AMOUNT_L,0) DEP_AMOUNT_L, 
   nvl(DEP_AMOUNT_AMT_L,0) DEP_AMOUNT_AMT_L, 
   nvl(LOAN_AMOUNT_L,0) LOAN_AMOUNT_L, 
   nvl(LOAN_AMOUNT_AMT_L,0) LOAN_AMOUNT_AMT_L,
   nvl(WARL,0) WARL,
   '' AS WARL_NUMS
  from DM.MID_T_MTH_CURRENT_M_AMONT 
 where FISCAL_MTH_DIMNSN_ID= '#V_DATA_MONTH#'
 
 union all
 
 select
 -1 DATA_AREA_CODE, 
 nvl(SUM(DEP_AMOUNT),0) DEP_AMOUNT, 
 nvl(SUM(DEP_AMOUNT_AMT),0) DEP_AMOUNT_AMT, 
 nvl(SUM(LOAN_AMOUNT),0) LOAN_AMOUNT, 
 nvl(SUM(LOAN_AMOUNT_AMT),0)  LOAN_AMOUNT_AMT, 
 nvl(SUM(DEP_AMOUNT_L),0) DEP_AMOUNT_L, 
 nvl(SUM(DEP_AMOUNT_AMT_L),0) DEP_AMOUNT_AMT_L, 
 nvl(SUM(LOAN_AMOUNT_L),0) LOAN_AMOUNT_L, 
 nvl(SUM(LOAN_AMOUNT_AMT_L),0) LOAN_AMOUNT_AMT_L,
 nvl(cast(sum(B.WARL) as double),0) WARL,
 '' AS WARL_NUMS
 from DM.MID_T_MTH_CURRENT_M_AMONT A
  LEFT JOIN(
          select  
            -1     AREA_NO_ID,          
            cast(CASE WHEN SUM(LOAN_AMOUNT) IS NULL THEN 0
                 WHEN SUM(LOAN_AMOUNT) ='' THEN 0
                 ELSE round(SUM(LOAN_AMOUNT_AMT)*1.000 /SUM(LOAN_AMOUNT),4) END
                 -
            CASE WHEN SUM(DEP_AMOUNT) IS NULL THEN 0
                 WHEN SUM(DEP_AMOUNT) ='' THEN 0
                 ELSE round(SUM(DEP_AMOUNT_AMT)*1.0000 /SUM(DEP_AMOUNT),4) END as double) WARL                                         
     from DM.MID_T_MTH_CURRENT_M_AMONT
    WHERE 
          FISCAL_MTH_DIMNSN_ID BETWEEN '#V_LAST_13M_END_MONTH#' AND '#V_LAST_1M_END_MONTH#' and  AREA_NO_ID!=-1 
   ) B ON A.AREA_NO_ID= B.AREA_NO_ID 
 where FISCAL_MTH_DIMNSN_ID='#V_DATA_MONTH#'
;